package servlets;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class ConsultationRecordDAO {
    
    private static final String CREATE_CONSULTATION_RECORD = "INSERT INTO qdlc_consultation_record (complaints, diagnosis, prescription, patientId, staffId, price, date) VALUES (?,?,?,?,?,?,?);";
    private static final String UPDATE_CONSULTATION_RECORD = "UPDATE qdlc_consultation_record SET complaints = ?, diagnosis = ?, prescription = ?, price = ?, date = ? WHERE consultationRecordId = ?";
    private static final String GET_CONSULTATION_RECORD_FROM_ID = "SELECT * FROM qdlc_consultation_record WHERE consultationRecordId = ?;";
    private static final String GET_ALL_CONSULTATION_RECORD = "SELECT * FROM qdlc_consultation_record;";
    private static final String GET_CONSULTATION_RECORDS_BY_PATIENT = "SELECT * FROM qdlc_consultation_record WHERE patientId = ?";
    
    private SimpleDateFormat formatter = new SimpleDateFormat(
            "yyyy-MM-dd HH:mm:ss");
    
    
    public ConsultationRecord createConsultationRecord(String complaints, String diagnosis, String prescription, int patientId, int staffId, float price, Date date) {
        ConsultationRecord cr = null;

        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(CREATE_CONSULTATION_RECORD,
                    PreparedStatement.RETURN_GENERATED_KEYS);

            st.setString(1, complaints);
            st.setString(2, diagnosis);
            st.setString(3, prescription);
            st.setInt(4, patientId);
            st.setInt(5, staffId);
            st.setFloat(6, price);
            st.setString(7, formatter.format(date));

            int affectedRows = st.executeUpdate();

            // Don't attempt to get keys if there is no affected row
            if (affectedRows > 0) {
                ResultSet keys = st.getGeneratedKeys();

                // Get key, return newUser
                if (keys.next()) {
                    int consultationRecordId = keys.getInt(1);

                    cr = new ConsultationRecord();
                    cr.setConsultationRecordId(consultationRecordId);
                    cr.setComplaints(complaints);
                    cr.setDiagnosis(diagnosis);
                    cr.setPrescription(prescription);
                    cr.setPatientId(patientId);
                    cr.setStaffId(staffId);
                    cr.setPrice(price);
                    cr.setDate(date);
                    
                    if (keys != null) {
                        keys.close();
                    }
                }
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return cr;
    }

    public void updateConsultationRecord(String complaints, String diagnosis, String prescription, float price, Date date, int consultationRecordId) {
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        try {
            PreparedStatement st = conn.prepareStatement(UPDATE_CONSULTATION_RECORD);
            st.setString(1, complaints);
            st.setString(2, diagnosis);
            st.setString(3, prescription);
            st.setFloat(4, price);
            st.setString(5, formatter.format(date));
            st.setInt(6, consultationRecordId);
            
            st.executeUpdate();

            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }
    }

    public ConsultationRecord getConsultationRecord(int consultationRecordId) {
        ConsultationRecord cr = null;
    
        // TODO: Add exceptions
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(GET_CONSULTATION_RECORD_FROM_ID);

            st.setInt(1, consultationRecordId);

            ResultSet rs = st.executeQuery();

            // Found result!
            if (rs.next()) {
                cr = new ConsultationRecord();
                cr.setConsultationRecordId(rs.getInt("consultationRecordId"));
                cr.setComplaints(rs.getString("complaints"));
                cr.setDiagnosis(rs.getString("diagnosis"));
                cr.setPrescription(rs.getString("prescription"));
                cr.setPatientId(rs.getInt("patientId"));
                cr.setStaffId(rs.getInt("staffId"));
                cr.setPrice(rs.getFloat("price"));
                cr.setDate(formatter.parse(rs.getString("date")));
            }

            if (rs != null) {
                rs.close();
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return cr;
    }
    
	public ArrayList<ConsultationRecord> geConsultationRecordByPatientId (
			Integer patientId) {
		ArrayList<ConsultationRecord> crList = new ArrayList<ConsultationRecord>();
		Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

		// Build query
		try {
			PreparedStatement st = conn
					.prepareStatement(GET_CONSULTATION_RECORDS_BY_PATIENT);

			st.setInt(1, patientId);

			ResultSet rs = st.executeQuery();

			// Found result!
			while (rs.next()) {
				ConsultationRecord cr = new ConsultationRecord();

				cr.setConsultationRecordId(rs.getInt("consultationRecordId"));
				cr.setComplaints(rs.getString("complaints"));
				cr.setDiagnosis(rs.getString("diagnosis"));
				cr.setPrescription(rs.getString("prescription"));
				cr.setPatientId(rs.getInt("patientId"));
				cr.setStaffId(rs.getInt("staffId"));
				cr.setPrice(rs.getFloat("price"));
				cr.setDate(formatter.parse(rs.getString("date")));

				crList.add(cr);
			}

			if (rs != null) {
				rs.close();
			}

			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		if(crList.size() > 0){
			return crList;
		} else {
			return null;
		}
	}
    
    public ArrayList<ConsultationRecord> getAllConsultationRecord() {
        ArrayList<ConsultationRecord> crList = new ArrayList<ConsultationRecord>();
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(GET_ALL_CONSULTATION_RECORD);

            ResultSet rs = st.executeQuery();

            // Found result!
            while (rs.next()) {
                ConsultationRecord cr = new ConsultationRecord();
    
                cr.setConsultationRecordId(rs.getInt("consultationRecordId"));
                cr.setComplaints(rs.getString("complaints"));
                cr.setDiagnosis(rs.getString("diagnosis"));
                cr.setPrescription(rs.getString("prescription"));
                cr.setPatientId(rs.getInt("patientId"));
                cr.setStaffId(rs.getInt("staffId"));
                cr.setPrice(rs.getFloat("price"));
                cr.setDate(formatter.parse(rs.getString("date")));
                
                crList.add(cr);
            }

            if (rs != null) {
                rs.close();
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return crList;
    }
    
}